﻿using Dapper;
using System;
using System.Text;
using System.Linq;
using SilenthinkContract;
using System.Collections.Generic;
using SilenthinkContract.Domain;

namespace SilenthinkService
{
    public class OrderService : ServiceBase, IOrderService
    {
        #region Order

        public bool CreateOrder(Order o)
        {
            bool result;
            const string field = @"([Id],[UserId],[Category],[Quantity],[SubTotal],[ShippingCharge],[Discount],[Total],[Paid],[Refunded],[Weight],[ShippingMethod],[PaymentMethod],[PaidDate],[ShippingDate],[DeliveryDate],[OrderStatus],[ShippingStatus],[PaymentStatus],[AssociatedId],[Expiration],[AreaCode],[IpAddress],[TraceCode],[CreatedBy],[CreatedOn])
                                VALUES(@Id,@UserId,@Category,@Quantity,@SubTotal,@ShippingCharge,@Discount,@Total,@Paid,@Refunded,@Weight,@ShippingMethod,@PaymentMethod,@PaidDate,@ShippingDate,@DeliveryDate,@OrderStatus,@ShippingStatus,@PaymentStatus,@AssociatedId,@Expiration,@AreaCode,@IpAddress,@TraceCode,@CreatedBy,@CreatedOn)";
            var builder = new SqlBuilder()
                .Table(Database.Order.Table.Order).Field(field)
                .AddParameters(new { o.Id, o.UserId, o.Category, o.Quantity, o.SubTotal, o.ShippingCharge, o.Discount, o.Total, o.Paid, o.Refunded, o.Weight, o.ShippingMethod, o.PaymentMethod, o.PaidDate, o.ShippingDate, o.DeliveryDate, o.OrderStatus, o.ShippingStatus, o.PaymentStatus, o.AssociatedId, o.Expiration, o.AreaCode, o.IpAddress, o.TraceCode, o.CreatedBy, o.CreatedOn });

            var template = builder.AddTemplate(SqlTemplate.Insert);

            var builderA = new SqlBuilder()
                .Table(Database.Order.Table.OrderItem)
                .Field("([UserId],[OrderId],[ProductId],[ProductName],[ProductVariantId],[ProductVariantName],[Quantity],[Discount],[UnitPrice],[ProductAttribute],[ProductAttributeXml],[CreatedBy],[CreatedOn])VALUES(@UserId,@OrderId,@ProductId,@ProductName,@ProductVariantId,@ProductVariantName,@Quantity,@Discount,@UnitPrice,@ProductAttribute,@ProductAttributeXml,@CreatedBy,@CreatedOn)");
            
            var templateA = builderA.AddTemplate(SqlTemplate.Insert);

            using (var connection = GetConnection(ConnectionName.Order).EnsureOpen())
            using (var transaction = connection.BeginTransaction())
            {
                try
                {
                    result = Execute(connection, template, transaction) == 1;
                    if (result)
                    {
                        ProcessOrder(o);
                        result = BatchInsert(connection, templateA.RawSql, o.OrderItems, transaction) > 0;
                    }
                    if (!result)
                    {
                        transaction.Rollback();
                    }
                    else
                    {
                        transaction.Commit();
                    }
                }
                catch (Exception e)
                {
                    Log.Trace(e.Message);
                    transaction.Rollback();
                    result = false;
                }
            }
            return result;

        }

        public bool UpdateOrder(Order o)
        {
            throw new NotImplementedException();
        }

        public bool DeleteOrder(string id)
        {
            throw new NotImplementedException();
        }

        public string GetAuctionOrderId(long userId, long auctionId)
        {
            var builder = new SqlBuilder().Field("[Id]")
                .Table(Database.Order.Table.Order)
                .Where("[OrderStatus]<2 AND [PaymentStatus]=0")
                .Where("[UserId]=@UserId", new {userId})
                .Where("AssociatedId=@AssociatedId", new {associatedId = auctionId.ToString()});

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Order))
            {
                return Query<string>(connection, template).FirstOrDefault();
            }
        }

        public Order GetOrder(string id)
        {
            return GetOrder(id, null);
        }

        public Order GetOrder(string id, long? userId = null, bool inclExtra = false, bool inclItem = false,
                              bool inclNote = false)
        {
            Order o;
            SqlBuilder.Template template;
            var builder = new SqlBuilder();

            var sb = new StringBuilder();
            if (userId.HasValue && userId.Value > 0)
            {
                sb.AppendFormat("SELECT TOP 1 * FROM {0} WHERE [Id]=@Id AND [UserId]=@UserId;",
                                Database.Order.Table.Order);
            }
            else
            {
                sb.AppendFormat("SELECT TOP 1 * FROM {0} WHERE [Id]=@Id;",
                                Database.Order.Table.Order);
            }
            if (inclExtra)
            {
                sb.AppendFormat("SELECT TOP 1 * FROM {0} WHERE [OrderId]=@Id;",
                                Database.Order.Table.OrderExtra);
            }
            if (inclItem)
            {
                sb.AppendFormat("SELECT * FROM {0} WHERE [OrderId]=@Id;",
                                Database.Order.Table.OrderItem);
            }

            if (inclNote)
            {
                sb.AppendFormat("SELECT * FROM {0} WHERE [OrderId]=@Id;",
                                Database.Order.Table.OrderNote);
            }
            if (userId.HasValue && userId.Value > 0)
            {
                template = builder.AddTemplate(sb.ToString(), new {id, userId});

            }
            else
            {
                template = builder.AddTemplate(sb.ToString(), new {id});
            }

            using (var connection = GetConnection(ConnectionName.Order))
            using (var multi =connection.QueryMultiple(template))
            {
                o = multi.Read<Order>().FirstOrDefault();
                if (o != null)
                {
                    if (inclExtra)
                    {
                        o.OrderExtra = multi.Read<OrderExtra>().First();
                    }
                    if (inclItem)
                    {
                        o.OrderItems = multi.Read<OrderItem>().ToList();
                    }
                    if (inclNote)
                    {
                        o.OrderNotes = multi.Read<OrderNote>().ToList();
                    }
                }
            }
            return o;
        }

        public PageList<Order> GetPagedOrder(int index = 1, int size = 20, string username = null, long? userId = null,
                                             OrderStatus? orderStatus = null, PaymentStatus? paymentStatus = null,
                                             ShippingStatus? shippingStatus = null, DateTime? beginDate = null,
                                             DateTime? endDate = null)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Order.Table.Order)
                .OrderBy("[CreatedOn] DESC")
                .AddParameters(new {begin = (index - 1)*size + 1, end = index*size});

            if (!string.IsNullOrEmpty(username))
            {
                builder.Where("[CreatedBy]=@UserName", new { username });
            }
            if (userId.HasValue && userId.Value > 0)
            {
                builder.Where("[UserId]=@UserId", new {userId});
            }

            var template = builder.AddTemplate(SqlTemplate.PagedSelect);

            using (var connection = GetConnection(ConnectionName.Order))
            {
                return PageList<Order>(connection, template);
            }
        }

        public IList<OrderItem> GetOrderItems(string[] orderIds)
        {
            //var ids = orderIds.Aggregate((a, b) => "'" + a + "','" + b + "'").TrimEnd(',');
            var sb = new StringBuilder();
            foreach (var i in orderIds)
            {
                sb.Append("'" + i + "',");
            }
            var ids = sb.ToString().TrimEnd(',');
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Order.Table.OrderItem)
                .Where("[OrderId] in (" + ids + ")");

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Order))
            {
                return Query<OrderItem>(connection, template).ToList();
            }
        }

        private static void ProcessOrder(Order o)
        {
            if (!o.OrderItems.Any()) return;
            foreach (var item in o.OrderItems)
            {
                item.OrderId = o.Id;
            }
        }

        #endregion

        #region Implementation of IService

        public int GetCachedSQLCount()
        {
            return SqlMapper.GetCachedSQLCount();
        }

        public List<Tuple<string, string, int>> GetCachedSQL()
        {
            return SqlMapper.GetCachedSQL().ToList();
        }

        public void PurgeQueryCache()
        {
            SqlMapper.PurgeQueryCache();
        }

        #endregion

    }
}